﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI.WebControls;
using System.Windows.Forms;
using MiniExcelLibs;
using MySql.Data.MySqlClient;
using Sunny.UI;
using static System.ComponentModel.Design.ObjectSelectorEditor;

namespace WindowsBB
{
    public partial class FormBB : UIForm
    {
        public static string conn = "Data Source=192.168.1.49;DataBase=UFDATA_009_2022;uid=bb;pwd=qwerasdf@";
        public static DataTable table = new DataTable();
        public FormBB()
        {
            InitializeComponent();
        }

        private void uiButton查询_Click(object sender, EventArgs e)
        {
            string tbname = "[Temp" + Guid.NewGuid().ToString() + "]";

            string sql = @" SELECT     账套,'U8 ' 类型, dPODate, csocode, cInvCode,'88888888888888888888888888888888888' 费用类型, cInvName, cInvStd, iQuantity, iUnitPrice, iMoney,iMoney ko_sch_amt,iMoney log_act_amt into " + tbname;
            sql += " FROM      View_采购订单    where 1=1 ";
            if (this.uiTextBox项目号.Text != "")
            {
                sql += " and csocode like '" + this.uiTextBox项目号.Text.Replace("-", "") + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='全加工'    ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update "+tbname+ " set 费用类型='原材料' where cInvCode like '%*'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='机械标准件' where len(cInvCode)=7 and 账套='002' ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='电气标准件' where cInvCode like '1%' and len(cInvCode)=5 and 账套 in ('003','009') ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='机械标准件' where cInvCode not like '1%' and len(cInvCode)=5 and 账套 in ('003','009')";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            //塞mes的
            sql = "insert into " + tbname + " (账套,类型, dPODate, csocode, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
            sql += " select 'MES','MES',log_rec_date,job_code,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1  ";

            if (this.uiTextBox项目号.Text != "")
            {
                sql += " and job_code like '" + this.uiTextBox项目号.Text.Replace("-", "") + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            if (!this.uiCheckBox汇总.Checked)
            {
                sql = "select 账套, dPODate 日期, csocode 项目号, cInvCode 物料号, 费用类型, cInvName 物料名称, cInvStd 规格型号,  ko_sch_amt 计划金额,log_act_amt 实际金额 from " + tbname;
                sql += " order by 账套,csocode,费用类型 ,cInvCode ";
                DataTable tb = new DataTable();
                //二次处理tb

                tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
                this.uiDataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                this.uiDataGridView1.DataSource = tb;
                table = tb;
                this.uiDataGridView1.Refresh();
              
                sql = "drop table  " + tbname;
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            }
            else
            {

                sql = @"	 select a.csocode,a.计划金额,a.实际金额,b.类型,b.计划金额,b.实际金额 ,c.费用类型,c.计划金额,c.实际金额
	 from 
	 (select csocode ,convert(nvarchar(100), sum(ko_sch_amt))计划金额,convert(nvarchar(100),sum(log_act_amt)) 实际金额 from " + tbname + " group by  csocode ) a ";
                sql += @" join 
	 (select csocode,类型,convert(nvarchar(100),sum(ko_sch_amt)) 计划金额,convert(nvarchar(100),sum(log_act_amt)) 实际金额 from  " + tbname + "  group by 类型,csocode ) b on a.csocode=b.csocode ";
                sql += @" join 
	 (select csocode,类型,费用类型,convert(nvarchar(100),sum(ko_sch_amt)) 计划金额,convert(nvarchar(100),sum(log_act_amt)) 实际金额 from  " + tbname + "  group by 类型,csocode,费用类型) c";
                sql += @" on (a.csocode=c.csocode  and b.csocode=c.csocode and b.类型=c.类型 )
	 order by a.csocode asc ,b.类型 desc,c.费用类型";
                DataTable tb = new DataTable();
                //二次处理tb

                tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
                this.uiDataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                this.uiDataGridView1.DataSource = tb;
                table = tb;
        //        this.uiDataGridView1.Refresh();
        //string 第0列 = tb.Rows[0][0].ToString();
        //        int s = 0;
        //        for (int i = 1; i < tb.Rows.Count; i++)
        //        {
        //            //第0列
        //            if (第0列== tb.Rows[i][0].ToString())
        //            {
        //                MergeCells(uiDataGridView1,s,i,0,Color.White);
        //                MergeCells(uiDataGridView1, s, i, 1, Color.White);
        //                MergeCells(uiDataGridView1, s, i, 2, Color.White);
        //            }
        //            else
        //            {
        //                s = i;
        //                第0列 = tb.Rows[i][0].ToString();
        //            }

        //        }

                sql = "drop table  " + tbname;
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            }

       


        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;
            
             
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, table, true, "sheet1", ExcelType.XLSX);
                    
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
            
        }

        

        private void uiDataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            //if (e.RowIndex >= 0 && (e.ColumnIndex == 0 || e.ColumnIndex == 3 || e.ColumnIndex == 6))
            //{
            //    // 不显示单元格边框
            //    e.AdvancedBorderStyle.All = DataGridViewAdvancedCellBorderStyle.None;
            //}
            //如果是 第一列，并且内容一样，就合并
            if (e.ColumnIndex == 0)
            {
                if (e.RowIndex == 0)
                {
                    return;
                }
                if (e.RowIndex > 0 && this.uiDataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == this.uiDataGridView1.Rows[e.RowIndex - 1].Cells[0].Value.ToString())
                {
                    e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None;
                    e.Handled = true;
                    return;
                }
            }

        }

        }
        public void MergeCells(DataGridView dataGridView, int startRow, int endRow, int column, Color color)
        {
            if (dataGridView.Columns.Count <= column || startRow < 0 || endRow >= dataGridView.Rows.Count || startRow >= endRow)
            {
                return;
            }

            DataGridViewCell cell = dataGridView[column, startRow];
            string cellValue = cell.Value != null ? cell.Value.ToString() : "";

            for (int i = startRow; i <= endRow; i++)
            {
                dataGridView[column, i].Value = "";
            }
            //在中间的行写值
            int valRowIndex = 0;
            int rows = endRow - startRow + 1;
            int halfRow = rows / 2;
            valRowIndex = startRow + halfRow;

            DataGridViewTextBoxCell textBoxCell = new DataGridViewTextBoxCell();
            textBoxCell.Value = cellValue;
            dataGridView[column, valRowIndex] = textBoxCell;


            for (int i = startRow; i <= endRow; i++)
            {
                dataGridView[column, i].Style.BackColor = color;
                dataGridView[column, i].ReadOnly = true; // 设置合并后的单元格只读
                dataGridView[column, i].Style.Alignment = DataGridViewContentAlignment.MiddleCenter; // 设置文本居中
                dataGridView[column, i].Style.Padding = new Padding(0); // 设置内边距为0，达到不显示边框的效果
                dataGridView[column, i].Style.SelectionBackColor = dataGridView[column, i].Style.BackColor; // 设置选中背景色与背景色一致
            }
        }
    }
}
